package com.seu.project.seuquiz.util;

import cn.hutool.core.collection.CollUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.seu.project.seuquiz.common.ErrorCode;
import com.seu.project.seuquiz.exception.BusinessException;
import com.seu.project.seuquiz.model.entity.*;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.util.DigestUtils;
import org.springframework.util.ResourceUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

/**
 * Excel 相关工具类
 */
@Slf4j
public class EasyExcelUtils {

    public static final String SALT="linhuaixu";

    /**
     * 读取excel文件，生成用户列表
     * @return
     */
    public static List<User> excelToUserList(MultipartFile multipartFile) {
/*        File file = null;
        try {
            file = ResourceUtils.getFile("classpath:test_user.xlsx");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }*/

        // 读取excel数据
        List<Map<Integer, String>> list = null;
        try {
            list = EasyExcel.read(multipartFile.getInputStream())
                    .excelType(ExcelTypeEnum.XLSX)
                    .sheet()
                    .headRowNumber(0)
                    .doReadSync();
        } catch (IOException e) {
            log.error("表格处理错误");
        }

        System.out.println(list);

        List<User> userList = new ArrayList<>();
        for (int i = 1; i < list.size(); i++) {
            Map<Integer, String> map = list.get(i);
            User user = new User();
            if (map.get(1) == null || map.get(2) == null || map.get(3) == null || map.get(4) == null) {
                throw new BusinessException(ErrorCode.PARAMS_ERROR, "学号、邮箱、学院、专业不能为空");
            }
            user.setUserName(map.getOrDefault(0, ""));
            user.setStudentID(map.get(1));
            user.setEmail(map.get(2));
            user.setCollege(map.get(3));
            user.setMajor(map.get(4));
            user.setUserAvatar(map.getOrDefault(5, ""));
            if (map.containsKey(6)) {
                user.setGender(Integer.valueOf(map.get(6)));
            }
            user.setUserRole(map.getOrDefault(7, "user"));
            String pwd = SALT + map.getOrDefault(8,  "12345678");
            String encryptPassword= DigestUtils.md5DigestAsHex(pwd.getBytes());
            user.setUserPassword(encryptPassword);
            userList.add(user);
        }

        System.out.println(userList);
        return userList;
    }

    /**
     * 读取excel文件，生成题目列表
     * @return
     */
    public static List<Question> excelToQuestionList(MultipartFile multipartFile) {
//        File file = null;
//        try {
//            file = ResourceUtils.getFile("classpath:test_question.xlsx");
//        } catch (FileNotFoundException e) {
//            e.printStackTrace();
//        }

        // 读取excel数据
        List<Map<Integer, String>> list = null;
        try {
            list = EasyExcel.read(multipartFile.getInputStream())
                    .excelType(ExcelTypeEnum.XLSX)
                    .sheet()
                    .headRowNumber(0)
                    .doReadSync();
        } catch (Exception e) {
            log.error("表格处理错误");
        }

        System.out.println(list);

        List<Question> questionList = new ArrayList<>();
        for (int i = 1; i < list.size(); i++) {
            Map<Integer, String> map = list.get(i);
            Question question = new Question();
            if (map.get(0) == null || map.get(1) == null || map.get(2) == null || map.get(3) == null) {
                throw new BusinessException(ErrorCode.PARAMS_ERROR, "题目、选项、答案、题解不能为空");
            }
            if (map.get(5) == null || map.get(6) == null || map.get(7) == null || map.get(8) == null) {
                throw new BusinessException(ErrorCode.PARAMS_ERROR, "分值、题目类型、类型、难度不能为空");
            }

            question.setContent(map.get(0));
            question.setSelection(map.get(1));
            question.setAnswer(map.get(2));
            question.setAnalysis(map.get(3));
            question.setRemark(map.get(4));
            question.setScore(Integer.valueOf(map.get(5)));
            question.setQuestionType(map.get(6));
            question.setType(map.get(7));
            question.setDifficulty(Integer.valueOf(map.get(8)));
            question.setCorrectCnt(Integer.valueOf(map.get(9)));
            question.setWriteCnt(Integer.valueOf(map.get(10)));
            questionList.add(question);
        }

        System.out.println(questionList);
        return questionList;
    }

    public static void main(String[] args) {
        excelToContestList(null);
    }

    public static List<Contest> excelToContestList(MultipartFile multipartFile) {
        List<Map<Integer, String>> list = null;
        try {
            list = EasyExcel.read(multipartFile.getInputStream())
                    .excelType(ExcelTypeEnum.XLSX)
                    .sheet()
                    .headRowNumber(0)
                    .doReadSync();
        } catch (Exception e) {
            log.error("表格处理错误");
        }

        System.out.println(list);

        List<Contest> contestList = new ArrayList<>();
        for (int i = 1; i < list.size(); i++) {
            Map<Integer, String> map = list.get(i);
            Contest contest = new Contest();
            if (map.get(0) == null || map.get(1) == null || map.get(2) == null || map.get(3) == null) {
                throw new BusinessException(ErrorCode.PARAMS_ERROR, "比赛名称、开始时间、结束时间、单人可答题次数不能为空");
            }
            if (map.get(5) == null) {
                throw new BusinessException(ErrorCode.PARAMS_ERROR, "答题时间不能为空");
            }
            contest.setContestName(map.get(0));
            Date startTime = null, endTime = null;
            try {
                System.out.println(map.get(1));
                startTime = stringToDate(map.get(1));
                endTime = stringToDate(map.get(2));
            } catch (Exception e) {
                throw new BusinessException(ErrorCode.PARAMS_ERROR, "日期格式不正确");
            }
            contest.setStartTime(startTime);
            contest.setEndTime(endTime);
            contest.setAnswerTimes(Integer.valueOf(map.get(3)));
            contest.setTime(Integer.valueOf(map.get(4)));
            contest.setQuestionNum(map.get(5));
            contest.setQuestionList(map.get(6));

            contestList.add(contest);
        }
        return contestList;
    }

    private static Date stringToDate(String dateString) throws ParseException {
        SimpleDateFormat ft = new SimpleDateFormat("yyyy-MM-dd HH:mm");
        Date date = ft.parse(dateString);
        return date;
    }

    /**
     * excel 转 csv
     * @param multipartFile
     * @return
     */
    public static String excelToCsv(MultipartFile multipartFile) {
/*        File file = null;
        try {
            file = ResourceUtils.getFile("classpath:网站数据.xlsx");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }*/

        // 读取数据
        List<Map<Integer, String>> list = null;
        try {
            list = EasyExcel.read(multipartFile.getInputStream())
                    .excelType(ExcelTypeEnum.XLSX)
                    .sheet()
                    .headRowNumber(0)
                    .doReadSync();
        } catch (IOException e) {
            log.error("表格处理错误", e);
        }
        if (CollUtil.isEmpty(list)) {
            return "";
        }
        // 转换为 csv
        StringBuilder stringBuilder = new StringBuilder();
        // 1.读取表头   用 LinkedHashMap 保证读取数据是连续的
        LinkedHashMap<Integer, String> headerMap = (LinkedHashMap<Integer, String>) list.get(0);
        // 过滤为null的数据
        List<String> headerList = headerMap.values().stream().filter(ObjectUtils::isNotEmpty).collect(Collectors.toList());
        // 用 , 拼接成一个字符串
        stringBuilder.append(StringUtils.join(headerList, ',')).append("\n");
        // 读取数据
        for (int i = 1; i < list.size(); i++) {
            LinkedHashMap<Integer, String> dataMap = (LinkedHashMap<Integer, String>) list.get(i);
            List<String> dataList = dataMap.values().stream().filter(ObjectUtils::isNotEmpty).collect(Collectors.toList());
            stringBuilder.append(StringUtils.join(dataList, ',')).append("\n");
        }
        return stringBuilder.toString();
    }

    /**
     * 读取excel文件，生成用户竞赛列表
     * @param multipartFile
     * @return
     */
    public static List<UserScoreMap> excelToUserContestList(MultipartFile multipartFile) {
        // 读取excel数据
        List<Map<Integer, String>> list = null;
        try {
            list = EasyExcel.read(multipartFile.getInputStream())
                    .excelType(ExcelTypeEnum.XLSX)
                    .sheet()
                    .headRowNumber(0)
                    .doReadSync();
        } catch (IOException e) {
            log.error("表格处理错误");
        }

        System.out.println(list);

        List<UserScoreMap> userScoreMapList = new ArrayList<>();
        for (int i = 1; i < list.size(); i++) {
            Map<Integer, String> map = list.get(i);
            UserScoreMap userScoreMap = new UserScoreMap();
            if (StringUtils.isAnyBlank(map.get(0),map.get(1), map.get(2), map.get(3), map.get(4))) {
                throw new BusinessException(ErrorCode.PARAMS_ERROR, "学号、竞赛号、答题次数、时间、分数不能为空");
            }

            userScoreMap.setStudentId(map.get(0));
            userScoreMap.setContestId(map.get(1));
            userScoreMap.setTimes(Integer.valueOf(map.get(2)));
            userScoreMap.setTime(Integer.valueOf(map.get(3)));
            userScoreMap.setScore(Integer.valueOf(map.get(4)));

            userScoreMapList.add(userScoreMap);
        }

        System.out.println(userScoreMapList);
        return userScoreMapList;
    }

    /**
     * 读取excel文件，生成学院竞赛列表
     * @param multipartFile
     * @return
     */
    public static List<CollegeContestMap> excelToCollegeContestList(MultipartFile multipartFile) {
        // 读取excel数据
        List<Map<Integer, String>> list = null;
        try {
            list = EasyExcel.read(multipartFile.getInputStream())
                    .excelType(ExcelTypeEnum.XLSX)
                    .sheet()
                    .headRowNumber(0)
                    .doReadSync();
        } catch (IOException e) {
            log.error("表格处理错误");
        }


        List<CollegeContestMap> collegeContestMapList = new ArrayList<>();
        for (int i = 1; i < list.size(); i++) {
            Map<Integer, String> map = list.get(i);
            CollegeContestMap collegeContestMap = new CollegeContestMap();

            if (StringUtils.isAnyBlank(map.get(0), map.get(1), map.get(2), map.get(3), map.get(4))) {
                throw new BusinessException(ErrorCode.PARAMS_ERROR, "学院名，竞赛id，学院总人数，参赛人数，总分不能为空");
            }

            collegeContestMap.setCollegeName(map.get(0));
            collegeContestMap.setContestId(map.get(1));
            collegeContestMap.setCollegeStudentTotal(Integer.valueOf(map.get(2)));
            collegeContestMap.setJoinTotal(Integer.valueOf(map.get(3)));
            collegeContestMap.setTotalScore(Integer.valueOf(map.get(4)));

            collegeContestMapList.add(collegeContestMap);
        }

        System.out.println(collegeContestMapList);
        return collegeContestMapList;
    }

    /**
     * 读取excel文件，生成学生题目列表
     * @param multipartFile
     * @return
     */
    public static List<UserQuestionMap> excelToUserQuestionList(MultipartFile multipartFile) {
        // 读取excel数据
        List<Map<Integer, String>> list = null;
        try {
            list = EasyExcel.read(multipartFile.getInputStream())
                    .excelType(ExcelTypeEnum.XLSX)
                    .sheet()
                    .headRowNumber(0)
                    .doReadSync();
        } catch (IOException e) {
            log.error("表格处理错误");
        }


        List<UserQuestionMap> userQuestionMapList = new ArrayList<>();
        for (int i = 1; i < list.size(); i++) {
            Map<Integer, String> map = list.get(i);

            if (StringUtils.isAnyBlank(map.get(0), map.get(1), map.get(2), map.get(3))) {
                throw new BusinessException(ErrorCode.PARAMS_ERROR, "学生id，题目id，做题总次数，做对次数不能为空");
            }

            int total = Integer.parseInt(map.get(2));
            int correctCnt = Integer.parseInt(map.get(3));

            if (total < correctCnt) {
                throw new BusinessException(ErrorCode.PARAMS_ERROR, "第" +i + "条做题记录中做对次数不应高于总次数");
            }

            UserQuestionMap userQuestionMap = new UserQuestionMap();
            userQuestionMap.setStudentId(map.get(0));
            userQuestionMap.setQuestionId(map.get(1));
            userQuestionMap.setTotalCnt(total);
            userQuestionMap.setCorrectCnt(correctCnt);

            userQuestionMapList.add(userQuestionMap);
        }

        return userQuestionMapList;
    }
}
